package com.qipay.mapper;

import java.util.List;

public class SpChannelSqlProvider extends com.qipay.baas.mapper.SpChannelSqlProvider {
	public String selectByChannelTypeAndStatusAndRuleAndSpId(Byte channelType,
															 Integer orderAmount,List<Long> spIds) {
		String spidsSql = spIds.toString().replaceAll("\\[", "(").replaceAll("\\]", ")");
		String selectSql = "SELECT * FROM ((SELECT "+
				" sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, "+
				"sc.create_date "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"LEFT JOIN sp_channel_rule scr ON sc.`sp_id`=scr.`sp_id` AND sc.`channel_type`=scr.`channel_type` "+
				"WHERE sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 AND sp.delete_flag=0 "+
				"AND scr.`id` IS NULL "+
				"AND sc.sp_id in "+spidsSql+" ORDER BY sc.update_date LIMIT 1) "+
				"UNION "+
				"(SELECT "+
				"sc.id, sc.sp_id, sc.channel_type, sc.fee_recharge, sc.state, sc.update_date, "+
				"sc.create_date "+
				"FROM sp_channel sc INNER JOIN sp ON sc.sp_id=sp.id "+
				"LEFT JOIN sp_channel_rule scr ON sc.`sp_id`=scr.`sp_id` AND sc.`channel_type`=scr.`channel_type` "+
				"WHERE sc.channel_type = %s AND sc.`state`=0 AND sp.`state`=0 "+
				"AND scr.`current_total_amount` >= %s "+
				"AND scr.`min_order_amount` <= %s "+
				"AND scr.`max_order_amount` > %s "+
				"AND CASE ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`) > '24:00:00' WHEN 1 "+
				"THEN (CURTIME() BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`) "+
				"OR ADDTIME(CURTIME(),'24:00:00')BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`)) "+
				"ELSE (CURTIME() BETWEEN scr.`min_time_stop` AND ADDTIME(scr.`min_time_stop`,scr.`min_time_stop_duration`)) END "+
				"LIMIT 1))T "+
				"LIMIT 1 ";


		return String.format(selectSql, channelType,channelType,orderAmount,orderAmount,orderAmount);
	}
}